package com.study.springboot.jpa.dao;

import com.study.springboot.jpa.entity.CityHohel;
import com.study.springboot.jpa.entity.TCity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.persistence.EntityManager;
import java.util.List;
import java.util.Map;

public interface TCityRepository extends JpaRepository<TCity, Integer>, JpaSpecificationExecutor<TCity> {
    List<TCity> findByIdLessThanAndNameLike(int id, String name);
    /**
     * 通过旅店名称分页查询旅店以及城市的信息
     *
     * @param name     旅店名称
     * @param pageable 分页信息
     * @return Page<Object[]>
     */
    @Query(value = "select t1.name as cityName,t2.name as hotelName\n" +
            "from t_city t1\n" +
            "  left join t_hotel t2 on t2.city = t1.id\n" +
            "where t2.name = :name",
            countQuery = "select count(*)" +
                    "from t_city t1 \n" +
                    "  left join t_hotel t2 on t2.city = t1.id\n" +
                    "where t2.name = :name"
            , nativeQuery = true)
    Page<Map<String,String>> findCityAndHotel(@Param("name") String name, Pageable pageable);

    /**
     * 通过旅店名称分页查询旅店以及城市的信息
     *
     * @param name     旅店名称
     * @param pageable 分页信息
     * @return Page<Object[]>
     */
    @Query(value = "select t1.name as cityName,t2.name as hotelName\n" +
            "from t_city t1\n" +
            "  left join t_hotel t2 on t2.city = t1.id\n" +
            "where t2.name = :name", nativeQuery = true)
    List<Object[]> findCityAndHotel2(@Param("name") String name);


    /**
     * HQL通过旅店名称查询旅店以及城市的所有信息
     *
     * @return
     */
    @Query(value = "select new map(t1,t2) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name")
    List<Map<String, Object>> findCityAndHotelByHQL(@Param("name") String name);

    /**
     * 关联查询
     *
     * @return
     */
    @Query(value = "select new com.study.springboot.jpa.entity.CityHohel(t1.name AS cityName,t2.name AS hotelName) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name")
    List<CityHohel> findCityAndHotelByHQLResultObj(@Param("name") String name);

    /**
     * 关联查询
     *
     * @return
     */
    @Query(value = "select new com.study.springboot.jpa.entity.CityHohel(t1.name AS cityName,t2.name AS hotelName) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name",
            countQuery = "select count(*) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name")
    Page<CityHohel> findCityAndHotelAllSelf(@Param("name") String name, Pageable pageable);


    /**
     * 自定义查询
     * @param sql
     * @param entityManager
     * @return
     */
    default List customQuery(String sql, EntityManager entityManager) {
        return entityManager.createQuery(sql).getResultList();
    }
    @Query(value = "select id from t_city where name like(:name)", nativeQuery = true)
    public List<String> findIdsByName(@Param("name") String name);
}
